package com.example.springmvc.dao;

import java.math.BigDecimal;
//import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;



import com.example.springmvc.domain.Employee;

public class EmployeeDaoImpl implements EmployeeDao {

	private DataSource dataSource;
	
	public void setDataSource(DataSource dataSource){
		this.dataSource = dataSource;
	}
	
	public void insert(Employee employee) {
		String sql = "INSERT INTO EMPLOYEES(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, " +
				" EMAIL, PHONE_NUMBER) " +
				"VALUES(EMPLOYEES_SEQ.NEXTVAL, ?, ?, ?, ?)";
		
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		jdbcTemplate.update(sql, new Object[] {employee.getFirst_name(),
											   employee.getLast_name(),
											   employee.getEmail(),
											   employee.getPhone_number()});
		
		/*Connection conn = null;
		try {
			
			conn = dataSource.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, employee.getFirst_name());
			pstmt.setString(2, employee.getLast_name());
			pstmt.setString(3, employee.getEmail());
			pstmt.setString(4, employee.getPhone_number());
			
			pstmt.executeUpdate();
			pstmt.close();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			if(conn != null){
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}*/

	}
	
	public void insertBatch(final List<Employee> employees){
		String sql = "INSERT INTO EMPLOYEES ( EMPLOYEE_ID, FIRST_NAME, LAST_NAME, " +
					"EMAIL, PHONE_NUMBER) " + 
					"VALUES( EMPLOYEE_SEQ,NEXTVAL, ?, ?, ?, ?)";
		
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter(){
			
			public void setValues(PreparedStatement pstmt, int index) throws SQLException {
				Employee employee = employees.get(index);
				pstmt.setString(1, employee.getFirst_name());
				pstmt.setString(2, employee.getLast_name());
				pstmt.setString(3, employee.getEmail());
				pstmt.setString(4, employee.getPhone_number());
				
			}
			
			public int getBatchSize() {
				return employees.size();
			}
		});
	}
	
	public List<Employee> findAll(){
		String sql = "SELECT * FROM EMPLOYEES";
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		
		List<Employee> results = new ArrayList<Employee>();
		List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql);
		
		for(Map<String, Object> row : rows){
			Employee employee = new Employee();
			employee.setEmployee_id(((BigDecimal) row.get("EMPLOYEE_ID")).intValueExact());
			employee.setFirst_name((String) row.get("FIRST_NAME"));
			employee.setLast_name((String) row.get("LAST_NAME"));
			employee.setEmail((String) row.get("EMAIL"));
			employee.setPhone_number((String) row.get("PHONE_NUMBER"));
			
			results.add(employee);
		}
		
		return results;
	}
	
	public Employee findByEmployeeId(int employee_id) {
		String sql = "SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID=?";
		
		final Employee employee = new Employee();
		
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		jdbcTemplate.query(sql, new Object[]{ employee_id },
				new RowCallbackHandler(){

					public void processRow(ResultSet rs) throws SQLException {
						// Mapping
						employee.setEmployee_id(rs.getInt("EMPLOYEE_ID"));
						employee.setFirst_name(rs.getString("FIRST_NAME"));
						employee.setLast_name(rs.getString("LAST_NAME"));
						employee.setEmail(rs.getString("EMAIL"));
						employee.setPhone_number(rs.getString("PHONE_NUMBER"));
					}
		
				});
		return employee;
		/*Connection conn = null;
		
		try {
			conn = dataSource.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, employee_id);
			
			Employee employee = null;
			ResultSet rs = pstmt.executeQuery();
			
			if(rs.next()){
				employee = new Employee(rs.getInt("EMPLOYEE_ID"), rs.getString("FIRST_NAME"), 
										rs.getString("LAST_NAME"), rs.getString("EMAIL"),
										rs.getString("PHONE_NUMBER"));
			}
			rs.close();
			pstmt.close();
			
			return employee;
			
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			if(conn != null){
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
*/
	}

	public void update(Employee employee) {
		// TODO Auto-generated method stub

	}

	public void delete(Employee employee) {
		// TODO Auto-generated method stub

	}

}
